﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace Visual_Studio_Test
{
	using System.ComponentModel;
	using System.Reflection;

	using Excel;

	public class ExcelIOHandler
	{
		protected Excel.Application excelApp;

		protected ExcelIOHandler()
		{
			excelApp = new Excel.Application();
			excelApp.Visible = false;
		}

		~ExcelIOHandler()
		{
			if (excelApp != null) excelApp.Quit();
			excelApp = null;
		}

		public class ColumnInfo
		{
			public string Name { get; set; }
			public int Position { get; set; }
		}
	}

	public class ExcelIOReader : ExcelIOHandler
	{
		public List<ColumnInfo> Columns { get; private set; }

		public List<string[]> ReadExcelFile(string fullFileName)
		{
			var readWorkBook = excelApp.Workbooks.Open(fullFileName);
			Excel._Worksheet workSheet = excelApp.ActiveSheet;
			Columns = GetColumnInfo(workSheet);

			//rows and columns start at 1
			int i = 2;//skip row 1
			var rows = new List<string[]>();
			while (true)
			{
				var row = new string[Columns.Count];
				for (int j = 0; j < Columns.Count; j++)
					row[j] = string.Empty + workSheet.Cells[i, j + 1].Value2;
				rows.Add(row);
				i++;
				var nextRowFirstCell = workSheet.Cells[i, 1].Value2;
				if (string.IsNullOrWhiteSpace(string.Empty + nextRowFirstCell))
					break;
			}
			return rows;
		}

		private static List<ColumnInfo> GetColumnInfo(_Worksheet workSheet)
		{
			var columns = new List<ColumnInfo>();
			for (int j = 1; j < int.MaxValue; j++)
			{
				var value = workSheet.Cells[1, j].Value2;
				if (string.IsNullOrWhiteSpace(value))
				{
					break;
				}
				columns.Add(new ColumnInfo { Name = value, Position = j });
			}
			return columns;
		}
	}

	public class ExcelIOWriter : ExcelIOHandler
	{
		public void WriteExcelFile(List<string[]> rows, string fullFileName, string[] firstRow = null)
		{
			var workBook = excelApp.Workbooks.Add();
			Excel._Worksheet workSheet = excelApp.ActiveSheet;

			if (firstRow != null)
			{
				if (rows.First<string[]>().Length != firstRow.Length)
					throw new Exception("First row and rows don't have the same number of columns");
				rows.Insert(0, firstRow);
			}

			for (int i = 0; i < rows.Count; i++)
				for (int j = 0; j < rows[i].Length; j++)
					workSheet.Cells[i + 1, j + 1] = rows[i][j];

			excelApp.DisplayAlerts = false;
			workBook.SaveAs(fullFileName);
			excelApp.DisplayAlerts = true;
			workBook.Close();
		}
	}
}
